package com.iMusic.businessLogicLayer;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.iMusic.integrationLayer.DBConnection;
import com.iMusic.integrationLayer.DBConnectionManager;

public class FinancialReports {

	private String startDate = null;
	private String endDate = null;
	private DBConnectionManager dbConnMgr = null;
	private DBConnection dbConn = null;

	List<Accounting> accounting = null;

	public FinancialReports (String startDate, String endDate) {
		this.startDate = startDate;
		this.endDate = endDate;
		dbConnMgr = DBConnectionManager.getDBConnectionManager();
		dbConn = dbConnMgr.getDBConnection();;
		dbConn.connect();
	}


	/*
	 * Generates accounting information
	 */
	public void generateAccounting() throws SQLException {

		String stmt = "SELECT P.ProductId, P.ProductName, P.TypeId, P.CategoryId, P.Price, OP.DateCreated, OP.SumPrice " +
				"FROM OrderProduct OP, OrderContent OC, Product P, ProductType PT, CategoryType CT " +
				"WHERE OP.OrderID = OC.OrderId " +
				"AND OC.ProductId = P.ProductId " +
				"AND P.TypeId = PT.TypeID " +
				"AND P.CategoryId = CT.CategoryId " +
				"AND OP.Payed = true " +
				"AND OP.DateCreated between '"+ startDate +"' and '"+ endDate +"' " +
				"ORDER BY OP.DateCreated";

		ResultSet rs = dbConn.query(stmt);

		accounting = new ArrayList<Accounting>();

		while (rs != null && rs.next()) {

			int typeId = rs.getInt("P.TypeId");
			int categoryId = rs.getInt("P.CategoryId");
			int productId = rs.getInt("P.ProductId");

			String accountNumber = String.format("R%03d%03d%03d", typeId, categoryId, productId);
			String creditDebit = "C";

			Accounting item = new Accounting();
			item.setDateCreated(rs.getDate("OP.DateCreated"));
			item.setProductId(productId);
			item.setProductName(rs.getString("P.ProductName"));
			item.setAmount(rs.getFloat("P.Price"));
			item.setAccountNumber(accountNumber);
			item.setCreditDebit(creditDebit);

			accounting.add(item);
		}


		stmt = "SELECT OP.SumPrice, OP.DateCreated, S.Type, S.Price, T1.Type FedTaxType, T1.Percentage FedTax, T2.Type ProvTaxType, T2.Percentage ProvTax " +
					"FROM OrderProduct OP, Shipping S, Tax T1, Tax T2 " +
					"WHERE OP.Payed = true " +
					"AND OP.DateCreated between '"+ startDate +"' and '"+ endDate +"' " +
					"AND S.Type = OP.ShippingType " +
					"AND T1.Type = OP.FederalTaxType " +
					"AND T2.Type = OP.ProvincialTaxType";


		rs = dbConn.query(stmt);

		while (rs != null && rs.next()) {

			int provincialTaxType = rs.getInt("ProvTaxType");
			int federalTaxType = rs.getInt("FedTaxType");
			int shippingType = rs.getInt("S.Type");
			Float shippingPrice = rs.getFloat("S.Price");
			Float sumPrice = rs.getFloat("OP.SumPrice");
			Date dateCreated = rs.getDate("OP.DateCreated");
			Float fedTax = rs.getFloat("FedTax");
			Float provTax = rs.getFloat("ProvTax");
			
			String shippingAccountNumber = String.format("S%03d", shippingType);
			String fedTaxAccountNumber = String.format("F%03d", federalTaxType);
			String provTaxAccountNumber = String.format("P%03d", provincialTaxType);
			
			Float provTaxAmount = ((sumPrice-shippingPrice) - ((sumPrice-shippingPrice)/(1+provTax)));
			provTaxAmount = (float) (Math.floor(provTaxAmount * 100)) / 100;

			Float fedTaxAmount = ((sumPrice-shippingPrice-provTaxAmount) - ((sumPrice-shippingPrice-provTaxAmount)/(1+fedTax)));;
			fedTaxAmount = (float) (Math.floor(fedTaxAmount * 100)) / 100;
			
			// Add shipping
			Accounting item = new Accounting();
			item.setDateCreated(dateCreated);
			item.setAmount(shippingPrice);
			item.setAccountNumber(shippingAccountNumber);
			item.setCreditDebit("C");
			accounting.add(item);

			item = new Accounting();
			item.setDateCreated(dateCreated);
			item.setAmount(shippingPrice);
			item.setAccountNumber(shippingAccountNumber);
			item.setCreditDebit("D");
			accounting.add(item);

			// Add Federal Tax
			item = new Accounting();
			item.setDateCreated(dateCreated);
			item.setAmount(fedTaxAmount);
			item.setAccountNumber(fedTaxAccountNumber);
			item.setCreditDebit("C");
			accounting.add(item);

			item = new Accounting();
			item.setDateCreated(dateCreated);
			item.setAmount(fedTaxAmount);
			item.setAccountNumber(fedTaxAccountNumber);
			item.setCreditDebit("D");
			accounting.add(item);

			// Add Provincial Tax
			item = new Accounting();
			item.setDateCreated(dateCreated);
			item.setAmount(provTaxAmount);
			item.setAccountNumber(provTaxAccountNumber);
			item.setCreditDebit("C");
			accounting.add(item);
			
			item = new Accounting();
			item.setDateCreated(dateCreated);
			item.setAmount(provTaxAmount);
			item.setAccountNumber(provTaxAccountNumber);
			item.setCreditDebit("D");
			accounting.add(item);

		}

	}

	protected void finalize() {
		dbConn.disconnect();
		dbConnMgr.freeDBConnection(dbConn);
	}



	public class Accounting {

		int productId = 0;
		String productName = "";
		String accountNumber = "";
		Float amount = (float) 0.0;
		String creditDebit = "";
		Date dateCreated = null;

		public int getProductId() {
			return productId;
		}
		public void setProductId(int productId) {
			this.productId = productId;
		}
		public String getProductName() {
			return productName;
		}
		public void setProductName(String productName) {
			this.productName = productName;
		}
		public String getAccountNumber() {
			return accountNumber;
		}
		public void setAccountNumber(String accountNumber) {
			this.accountNumber = accountNumber;
		}
		public Float getAmount() {
			return amount;
		}
		public void setAmount(Float amount) {
			this.amount = amount;
		}
		public String getCreditDebit() {
			return creditDebit;
		}
		public void setCreditDebit(String creditDebit) {
			this.creditDebit = creditDebit;
		}
		public Date getDateCreated() {
			return dateCreated;
		}
		public void setDateCreated(Date dateCreated) {
			this.dateCreated = dateCreated;
		}


	}



	public List<Accounting> getAccounting() {
		return accounting;
	}


	public void setAccounting(List<Accounting> accounting) {
		this.accounting = accounting;
	}

}
